using System;
using System.Data;
using System.Data.SqlClient;

using Sunyi.Project.WebPoint.Common;
using Sunyi.Project.WebPoint.DataAccess;

using Sunyi.Project.WebPoint.DataContract;

namespace Sunyi.Project.WebPoint.DataAccess
{
    public partial class IssueDao : IDao<Issue>
    {
        public Issue Select(Guid id)
        {
            Issue issue = null;

            SqlConnection connection = ConnectionProvider.CreateConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "procTR_Issue_Select";
            command.Parameters.Add(new SqlParameter("@Id", id));

            try
            {
                connection.Open();

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        issue = CreateEntityByReader(reader);
                    }
                }
            }
            finally
            {
                ConnectionProvider.CloseConnection(connection);
            }

            return issue;
        }

        public void Insert(Issue issue)
        {
            SqlConnection connection = ConnectionProvider.CreateConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "procTR_Issue_Insert";
            command.Parameters.Add(new SqlParameter("@Id", issue.Id));
            command.Parameters.Add(new SqlParameter("@IssueTitle", issue.IssueTitle));
            command.Parameters.Add(new SqlParameter("@Description", issue.Description));
            command.Parameters.Add(new SqlParameter("@OwnerId", issue.OwnerId));
            command.Parameters.Add(new SqlParameter("@Comments", issue.Comments));
            command.Parameters.Add(new SqlParameter("@Status", issue.Status));
            command.Parameters.Add(new SqlParameter("@CreatorId", issue.CreatorId));
            command.Parameters.Add(new SqlParameter("@IsActive", issue.IsActive));

            foreach (SqlParameter parameter in command.Parameters)
            {
                if (parameter.Value == null || parameter.Value.ToString() == Guid.Empty.ToString()) parameter.Value = DBNull.Value;
            }

            try
            {
                connection.Open();
                command.ExecuteNonQuery();
            }
            finally
            {
                ConnectionProvider.CloseConnection(connection);
            }
        }

        public void Update(Issue issue)
        {
            if (issue.UpdateTimestamp == DateTime.MinValue) issue.UpdateTimestamp = DateTime.Now;

            SqlConnection connection = ConnectionProvider.CreateConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "procTR_Issue_Update";
            command.Parameters.Add(new SqlParameter("@Id", issue.Id));
            command.Parameters.Add(new SqlParameter("@IssueTitle", issue.IssueTitle));
            command.Parameters.Add(new SqlParameter("@Description", issue.Description));
            command.Parameters.Add(new SqlParameter("@OwnerId", issue.OwnerId));
            command.Parameters.Add(new SqlParameter("@Comments", issue.Comments));
            command.Parameters.Add(new SqlParameter("@Status", issue.Status));
            command.Parameters.Add(new SqlParameter("@CreatorId", issue.CreatorId));
            command.Parameters.Add(new SqlParameter("@IsActive", issue.IsActive));
            command.Parameters.Add(new SqlParameter("@UpdateTimestamp", DateTime.Now));

            foreach (SqlParameter parameter in command.Parameters)
            {
                if (parameter.Value == null || parameter.Value.ToString() == Guid.Empty.ToString()) parameter.Value = DBNull.Value;
            }

            try
            {
                connection.Open();
                command.ExecuteNonQuery();
            }
            finally
            {
                ConnectionProvider.CloseConnection(connection);
            }
        }

        public void Delete(Guid id)
        {
            SqlConnection connection = ConnectionProvider.CreateConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "procTR_Issue_Delete";
            command.Parameters.Add(new SqlParameter("@Id", id));

            try
            {
                connection.Open();
                command.ExecuteNonQuery();
            }
            finally
            {
                ConnectionProvider.CloseConnection(connection);
            }
        }

        #region Associated Entity Attachment Related

        const string Attachment_ColumnName = "IssueId";
        const string Attachment_TableName = "TR_Issue_Attachment";
        const string Attachment_ValueColumnName = "AttachmentId";

        public void FillAttachments(Issue issue)
        {
            DaoHelper.FillEntity<Guid>(issue.Attachments, issue.Id, Attachment_ColumnName, Attachment_TableName, Attachment_ValueColumnName);
        }

        public void SaveAttachments(Issue issue)
        {
            TransactionHelper.ProcessTransaction
            (() =>
                {
                    DaoHelper.RemoveEntity(issue.Id, Attachment_ColumnName, Attachment_TableName);
                    DaoHelper.InsertEntity<Guid>(issue.Attachments, issue.Id, Attachment_ColumnName, Attachment_TableName, Attachment_ValueColumnName);
                }
            );
        }

        #endregion

        #region Private Method

        internal static Issue CreateEntityByReader(IDataReader reader)
        {
            Issue issue = new Issue();
            if (reader["Id"] != DBNull.Value) issue.Id = (Guid)reader["Id"];
            if (reader["IssueTitle"] != DBNull.Value) issue.IssueTitle = reader["IssueTitle"] as string;
            if (reader["Description"] != DBNull.Value) issue.Description = reader["Description"] as string;
            if (reader["OwnerId"] != DBNull.Value) issue.OwnerId = (Guid)reader["OwnerId"];
            if (reader["Comments"] != DBNull.Value) issue.Comments = reader["Comments"] as string;
            if (reader["Status"] != DBNull.Value) issue.Status = reader["Status"] as string;
            if (reader["CreatorId"] != DBNull.Value) issue.CreatorId = (Guid)reader["CreatorId"];
            if (reader["IsActive"] != DBNull.Value) issue.IsActive = (Boolean)reader["IsActive"];
            if (reader["InsertTimestamp"] != DBNull.Value) issue.InsertTimestamp = (DateTime)reader["InsertTimestamp"];
            if (reader["UpdateTimestamp"] != DBNull.Value) issue.UpdateTimestamp = (DateTime)reader["UpdateTimestamp"];

            return issue;
        }

        #endregion
    }
}